Names in formulas and functions

When you work with large data sets, you can give names to frequently used cells, cell ranges, constants, and formulas. Names are used in formulas and functions and make them easier to write and understand.

For example, the function =SUM(Sales) is entered and understood more easily than the function =SUM(D2; D20).

Names are divided into two types:

•Defined: Names that the user manually assigned to cells, ranges, constants, and formulas.

•Table: The names of tables that have tabular formatting style applied in Microsoft Excel (also called "smart" tables). Smart table names are automatically created in Microsoft Excel.

 

In addition, names are varied by their area of application:

•Global: Can be used on any spreadsheet sheet.

•Local: Can be used only on the sheet on which they were set.

 

The following interface elements are used to work with names:

•Range field: The field is located to the left of the Formula bar. You can expand the range field if necessary. To do this, move the cursor to the right border of the field so that it looks like a bidirectional arrow, and while keeping the left mouse button pressed, move the field boundary to the right.

•Name Manager: It opens when you click the side_panel_defined_names Name Manager button on the Sidebar.

 

hmtoggle_arrow1 Define name

The name may contain the following elements:

•Letters

•Digits (cannot be used at the beginning of the name)

•Symbols

_ (underscore)

\ (backslash)

. (dot) (cannot be used at the beginning of the name)

 

The name can be set using the range field (quick way) or using the Name Manager.

Define name with range field

You can use a range field to name a cell or a range of cells. The name specified using the range field is global.

To specify a name using a range field, do the following:

1.Select the cell or range of cells to specify a name.

2.Enter a name in the range field.

3.To save the name, press Enter. If you want to delete the data you entered when creating the name, press Esc.

Define a name with Name Manager

You can use the Name Manager to name a cell, a range of cells, a constant, or a formula.

To specify a name using the Name Manager, follow these steps:

1.If you want to name a cell or a range of cells, select the desired cells on the sheet. If you want to name a constant or formula, skip this step.

2.Open the Name Manager in one of the following ways:

•In the Command menu, select Data > Define Name.

•Right-click the selected cell or cell range to open the context menu and run the Define Name command.

•On the Sidebar, click the side_panel_defined_names Name Manager button and in the Name Manager, click Define Name.

3.In the Name Manager, specify the data to create the name:

•In the Name field, specify the name of the cell/range/constant/formula or leave the default name. The default name is [Name_n], where n is the ordinal number of the named item.

•If a name is created for a cell/range, a reference to the cell/range selected in the first step is automatically generated in the Reference field. If necessary, the reference can be edited manually or you can select another cell/range on the sheet with the mouse so that the reference is edited automatically. If a name is created for a constant or formula, enter the desired constant/formula manually in the Reference field.

infoReferences to cells and cell ranges are displayed as absolute references by default. You can convert them to relative ones manually.

•In the drop-down list specify where the name will be used: on all sheets of the spreadsheet or on a particular sheet.

4.Click the Done button or press Enter.

 

The created name appears in the Name Manager list.

hmtoggle_arrow1 Find a name

The name can be found with a range field or by using the Name Manager.

infoCharacter case is not taken into account when searching for a name.

Find a name with the range field

To quickly search for a name, follow these steps:

1.Click the range field.

2.Start typing the name. A drop-down list displays the names that match the search conditions.

3.Select the name using the mouse or the keyboard:

•Left-click the name.

•Select the name with the ↓ and ↑ keys and press Enter.

 

To view the entire list of names and select the required name from it, follow these steps:

1.Expand the entire list of names. For this purpose, in the range field, click the expand button.

2.Select the required name from the list using the mouse or keyboard:

•Left-click the name.

•Select the name with the keys ↓ and ↑ and press Enter.

 

If the selected name belongs to a cell/range, that cell/range is highlighted in the spreadsheet.

If the selected name belongs to a constant or formula, the Name Manager opens and automatically selects a line for that name.

If the name of a cell/cell range/constant/formula is selected in the cell editing mode, it is entered in the cell as a formula/function value.

Find with Name Manager

Open the Name Manager in one of the following ways:

•On the Sidebar, click the side_panel_defined_names Name Manager button.

•In the range field, click the expand button and run the Manage Defined Names command.

 

The Name Manager displays a complete list of names that the current spreadsheet contains. Use the find and replace pane and filter/sort tools to quickly search through the list.

To search for a name using the find and replace pane, follow these steps:

1.Place the cursor in the find and replace pane.

2.Start typing the required name or the reference/constant/formula to which that name is assigned.

 

As you enter data for the search, the list of names will be reduced to values that match the search conditions.

To filter the list, follow these steps:

1.Click the All names field.

2.In the drop-down list, specify which group of names should be displayed in the list:

•Defined names: Names set manually by the user.

•Table names: Names of "smart" tables.

•Local: current sheet: Names that can be used on the sheet that is currently open.

•Global: all sheets: Names that can be used on any sheet.

 

If you want to select a way to sort the list, follow these steps:

1.Click the side_panel_defined_names_sorting button.

2.Select a sorting method from the drop-down list:

•Name (A-Z): sorting by the value specified in the Name field (in ascending order).

•Name (Z-A): sorting by the value specified in the Name field (in descending order).

•Reference (A-Z): sorting by the value specified in the Reference field (in ascending order).

•Reference (Z-A): sorting by the value specified in the Reference field (in descending order).

 

To see which cell/range corresponds to which name, select that name in the list. The cell/range will be highlighted in the spreadsheet.

If the name of a cell/cell range/constant/formula is selected in the cell editing mode, it is entered in the cell as a formula/function value.

hmtoggle_arrow1 Enter a name into formula or function

You can enter formulas and functions that contain names of cells/cell ranges/constants/formulas using standard methods.

You can enter a name into a formula or function in the following ways:

•Manually

•By means of a range field

•By means of the Name Manager

 

To enter a name manually, follow these steps:

1.Start typing the name. A drop-down list displays the names that match the search criteria.

2.Select the name using the mouse or keyboard:

•Left-click the name.

•Select the name with the keys ↓ and ↑ and press Enter.

 

If you want to enter a name using a range field, search for it as described in the Find a name with the range field section.

To enter a name using the Name Manager, proceed with the following:

1.Search for a name as described in the Find with Name Manager section.

2.Select a name using the mouse or keyboard:

•Select a name by double-clicking.

•Select the name with the keys ↓ and ↑ and press Enter.

 

The selected name will be inserted into the formula/function. If the name refers to a cell/range, the cell/range will be highlighted in the spreadsheet.

infoIf necessary, you can enter a local name from another sheet into a formula/function. To do this, select the local name in the range field or in the Name Manager or manually enter a reference such as 'Sheet Name'!MyName. For example: 'Sheet2'!Name_8.

hmtoggle_arrow1 View name properties

To view the properties of a name, proceed as follows:

1.Open Name Manager in one of the following ways:

•On the Sidebar, click the side_panel_defined_names Name Manager button.

•In the range field, click the expand button and run the Manage Defined Names command.

2.In the Name Manager, hover the cursor over the desired name and click the expand Expand button.

 

If the name belongs to a cell/range, that cell/range will be highlighted in the spreadsheet.

The Name Manager displays the properties of the selected name. The text in the Name and Reference fields can be copied if necessary.

To hide the name properties, click the Collapse button.

hmtoggle_arrow1 Delete a name

You can delete names created manually. You cannot delete smart table names.

To delete a name, follow these steps:

1.Open Name Manager in one of the following ways:

•On the Sidebar, click the side_panel_defined_names Name Manager button.

•In the range field, click the expand button and run the Manage Defined Names command.

2.In the Name Manager, hover the cursor over the desired name and click the expand Expand button.

3.Click side_panel_defined_names_delete_name Delete.

© New Cloud Technologies Ltd., 2013—2024
MyOffice Education 3.1E